class AddAccountBudgetView < ActiveRecord::Migration
  def up

    execute <<-SQL
CREATE VIEW "account_budget_rules" AS
select a.id, a.account_id, a.name, a.budget_limit,a.utilization, a.budget_limit + ifnull(sum(e.amount),0) as "budget_limit_w_exceptions" from (
SELECT  1 as "id", b.account_id, c.name as "name" ,b.amount as "budget_limit", case when  c.name <> 'Total budget' then ifnull(sum(p.amount),0) when  c.name = 'Total budget' then (select ifnull(sum(p1.amount),0) from payments p1 where p1.category_id <> c.id and p1.account_id = b.account_id)  end as "utilization", c.id as category_id
from categories c
left outer join budgets b on (c.id = b.category_id)
left outer join payments p on (p.account_id = b.account_id and p.category_id = b.category_id)
group by id, b.account_id, c.name ,b.amount, c.id) a
left outer join budget_exceptions e on (e.category_id = a.category_id and e.account_id = a.account_id)
group by  a.id, a.account_id, a.name, a.budget_limit,a.utilization
order by a.category_id

    SQL
  end
  def down
    execute <<-SQL
      DROP VIEW my_view
    SQL
  end



end

svn add test/unit/notification_test.rb
svn add test/unit/helpers/notifications_helper_test.rb
svn add test/functional/notifications_controller_test.rb
svn add test/fixtures/notifications.yml
svn add app/assets/javascripts/notifications.js.coffee
svn add app/assets/stylesheets/notifications.css.scss
svn add app/helpers/notifications_helper.rb
svn add app/models/notification.rb
svn add app/controllers/notifications_controller.rb
svn add app/views/notifications
.idea/dataSources.ids
.idea/dataSources.xml
.idea/dictionaries
tmp/cache/assets/DB1
tmp/cache/assets/CD2
tmp/cache/assets/D6D
tmp/cache/assets/D74/E70
tmp/cache/sass/e57a08ebd540563ac33012426afd8bf506801b98/notifications.css.scssc
